﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ProjecteCompartitSQL
{
    public partial class Form1 : Form
    {
        DataTable tAutors, tLlibres, tTemes;
        System.Data.SqlClient.SqlDataAdapter daAutors, daLlibres, daTemes;
        public SqlConnection con;
        SqlParameter parameter;
        public Form1()
        {
            InitializeComponent();
            tAutors = null; tLlibres = null; tTemes = null;
            daAutors = null; daLlibres = null; daTemes = null;
            #region perdua de temps
            /********************************/
            /*****  Creació taula Autors*****/
            /********************************/
            /*
            DataColumn idAutor = new DataColumn("id",typeof(int));
            idAutor.Unique = true;
            idAutor.AutoIncrement = true;
            idAutor.AutoIncrementSeed = 1;
            idAutor.AutoIncrementStep = 1;
            DataColumn nomAutor = new DataColumn("Nom",typeof(string));
            DataColumn paisAutor = new DataColumn("País", typeof(string));
            DataColumn dataNaixAutor = new DataColumn("Data Naixement", typeof(string));
           
            tAutors.Columns.AddRange(new DataColumn[]{  idAutor,
                                                        nomAutor,
                                                        paisAutor,
                                                        dataNaixAutor
                                                        });
            /********************************/
            /*****  Creació taula Llibres****/
            /********************************/
            /*
            DataColumn isbn = new DataColumn("ISBN",typeof(int));
            isbn.Unique = true;
            isbn.AutoIncrement = true;
            isbn.AutoIncrementSeed = 1;
            isbn.AutoIncrementStep = 1;
            DataColumn titolLlibre = new DataColumn("Títol", typeof(string));
            DataColumn editorialLlibre = new DataColumn("Editorial", typeof(string));
            DataColumn anyLlibre = new DataColumn("", typeof(DateTime));
            tLlibres.Columns.AddRange(new DataColumn[]{ isbn,
                                                        titolLlibre,
                                                        editorialLlibre,
                                                        anyLlibre
                                                      });
            /********************************/
            /*****  Creació taula Temes *****/
            /********************************/
            /*
            DataColumn idTemes = new DataColumn("id", typeof(int));
            idAutor.Unique = true;
            idAutor.AutoIncrement = true;
            idAutor.AutoIncrementSeed = 1;
            idAutor.AutoIncrementStep = 1;
            DataColumn tipusTema = new DataColumn("Nom", typeof(string));
            
            tAutors.Columns.AddRange(new DataColumn[]{  idTemes,
                                                        tipusTema,
                                                        });
            */
            #endregion
        }
        private void Form1_Load(object sender, EventArgs e)
        {

            String conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Connex"].ConnectionString;
            con = new SqlConnection(conStr);
            con.Open();

            tAutors = new DataTable("Autors");
            tLlibres = new DataTable("Llibres");
            tTemes = new DataTable("Temes");
            
            DGAutors.DataSource = tAutors;
            daAutors = new SqlDataAdapter("SELECT * FROM Autors", con);
            SqlCommandBuilder CBAutors = new SqlCommandBuilder(daAutors);


            daAutors.UpdateCommand = new SqlCommand("UPDATE Autors SET Nom = @Nom, dataNaixement = @dataNaixement, Pais = @Pais where idAutor = @idAutor", con);
            parameter = daAutors.UpdateCommand.Parameters.Add("@idAutor", SqlDbType.Int, 0, "idAutor");
            parameter.SourceVersion = DataRowVersion.Original;
            parameter = daAutors.UpdateCommand.Parameters.Add("@Nom", SqlDbType.VarChar, 0, "Nom");
            parameter.SourceVersion = DataRowVersion.Original;
            parameter = daAutors.UpdateCommand.Parameters.Add("@dataNaixement", SqlDbType.DateTime, 0, "dataNaixement");
            parameter.SourceVersion = DataRowVersion.Original;
            parameter = daAutors.UpdateCommand.Parameters.Add("@Pais", SqlDbType.VarChar, 0, "Pais");
            parameter.SourceVersion = DataRowVersion.Original;
            daAutors.DeleteCommand = new SqlCommand("DELETE FROM Autors WHERE idAutor = @idAutor", con);
            parameter = daAutors.DeleteCommand.Parameters.Add("@idAutor", SqlDbType.Int, 0, "idAutor");
            parameter.SourceVersion = DataRowVersion.Original;

            daAutors.Fill(tAutors);


            DGLlibres.DataSource = tLlibres;
            daLlibres = new SqlDataAdapter("SELECT * FROM Llibres", con);
            SqlCommandBuilder CBLlibres = new SqlCommandBuilder(daLlibres);

            daLlibres.UpdateCommand = new SqlCommand("UPDATE Llibres SET isbn = @isbn, Titol = @Titol, Editorial = @Editorial where isbn = @isbn", con);
            parameter = daLlibres.UpdateCommand.Parameters.Add("@isbn", SqlDbType.Int, 0, "isbn");
            parameter.SourceVersion = DataRowVersion.Original;
            parameter = daLlibres.UpdateCommand.Parameters.Add("@Titol", SqlDbType.VarChar, 0, "Tipus");
            parameter.SourceVersion = DataRowVersion.Original;
            parameter = daLlibres.UpdateCommand.Parameters.Add("@Editorial", SqlDbType.VarChar, 0, "Editorial");
            parameter.SourceVersion = DataRowVersion.Original;
            daLlibres.DeleteCommand = new SqlCommand("DELETE FROM Llibres WHERE isbn = @isbn", con);
            parameter = daLlibres.DeleteCommand.Parameters.Add("@isbn", SqlDbType.Int, 0, "isbn");
            parameter.SourceVersion = DataRowVersion.Original;

            daLlibres.Fill(tLlibres);


            DGTemes.DataSource = tTemes;
            daTemes = new SqlDataAdapter("SELECT * FROM Temes", con);
            SqlCommandBuilder CBTemes = new SqlCommandBuilder(daTemes);

            daTemes.UpdateCommand = new SqlCommand("UPDATE Temes SET Tipus = @Tipus where idTema = @idTema", con);
            parameter = daTemes.UpdateCommand.Parameters.Add("@idTema", SqlDbType.Int, 0, "idTema");
            parameter.SourceVersion = DataRowVersion.Original;
            parameter = daTemes.UpdateCommand.Parameters.Add("@Tipus", SqlDbType.VarChar, 0, "Tipus");
            parameter.SourceVersion = DataRowVersion.Original;
            daTemes.DeleteCommand = new SqlCommand("DELETE FROM Temes WHERE idTema = @idTema", con);
            parameter = daTemes.DeleteCommand.Parameters.Add("@idTema", SqlDbType.Int, 0, "idTema");
            parameter.SourceVersion = DataRowVersion.Original;

            
            daTemes.Fill(tTemes);
            con.Close();
        }
        private void BtnAfe_Click(object sender, EventArgs e)
        {
            int i = TCLlibre.SelectedIndex;

            if (i == 0)
            {
                
            }
            else if (i == 1)
            {
                this.Enabled = false;
                Form f = new Llibreria.MantAutors(tAutors);
                f.ShowDialog();
                daAutors.Update(tAutors);
                this.Enabled = true;
            }
            else
            {
                this.Enabled = false;
                Form f = new Llibreria.MantTemes(tTemes);            
                f.ShowDialog();
                daTemes.Update(tTemes);
                this.Enabled = true;
            }
            actualitzarDades();
        }


        private void button4_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
        private void actualitzarDades()
        {
            con.Open();
            tTemes.Clear();
            daTemes.Fill(tTemes);
            tAutors.Clear();
            daAutors.Fill(tAutors);
            tLlibres.Clear();
            daLlibres.Fill(tLlibres);
            con.Close();
        }

        private void BtnEdi_Click(object sender, EventArgs e)
        {
            int i = TCLlibre.SelectedIndex;

            if (i == 0)
            {

            }
            else if (i == 1)
            {
                this.Enabled = false;
                Form f = new Llibreria.MantAutors(tAutors, DGAutors.CurrentRow.Index);
                f.ShowDialog();
                daAutors.Update(tAutors);
                this.Enabled = true;
            }
            else
            {
               
                this.Enabled = false;
                Form f = new Llibreria.MantTemes(tTemes, DGTemes.CurrentRow.Index);
                f.ShowDialog();
                daTemes.Update(tTemes);
                this.Enabled = true;
                
             
            }
            actualitzarDades();
           
        }

        private void BtnBorr_Click(object sender, EventArgs e)
        {
            int i = TCLlibre.SelectedIndex;

            if (i == 0)
            {

            }
            else if (i == 1)
            {
                int j = DGAutors.CurrentRow.Index;
                tAutors.Rows[j].Delete();
                int ncanvis = daAutors.Update(tAutors);
                Console.Write("DEBUG ncanvis:" + ncanvis);
            }
            else
            {
                int j = DGTemes.CurrentRow.Index;
                tTemes.Rows[j].Delete();
                int ncanvis = daTemes.Update(tTemes);
                Console.Write("DEBUG ncanvis:" + ncanvis);
                
            }
            actualitzarDades();            
        }
 
    }
}
